package com.bw.utils;

import com.microsoft.schemas.office.visio.x2012.main.CellType;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



public class ReadExcel {
    public static List<Map> readExcel(String hou,InputStream in) throws Exception {
        List<Map> mapList =new ArrayList<Map>();
        Workbook wk=null;
        if(".xls".equals(hou)) {//03以前
            wk=new HSSFWorkbook(in);
        }
        if(".xlsx".equals(hou)) {//07以后
            wk=new XSSFWorkbook(in);
        }
        Sheet sheetAt = wk.getSheetAt(0);//获取第一页
        int lastRowNum = sheetAt.getLastRowNum();//最大行数
        System.out.println(lastRowNum);
        for (int i = 2; i <= lastRowNum; i++) {//从数据行开始读
            Row row = sheetAt.getRow(i);//获取对应行
            int lastCellNum = row.getLastCellNum();//获取最大列数
            Map<String, Object> mapinfo = new HashMap<>();//用于装每行的数据
            for (int j = 0; j < lastCellNum; j++) {//所有列读取
                String key = getKeyDate(sheetAt.getRow(1).getCell(j));//获取别名，作为key
                Object value=getDate(row.getCell(j));//对应行的每列数据分别判断处理
                mapinfo.put(key, value);
            }
            mapList.add(mapinfo);
        }
        return mapList;
    }
    public static String getKeyDate(Cell cell) {
        if(null==cell) {
            return null;
        }
        return cell.getStringCellValue();

    }
    public static Object getDate(Cell cell) {
        if(null==cell) {
            return null;
        }
        Object value=null;
        int ct = cell.getCellType();
        switch (ct) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value=cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)) {
                    value=cell.getDateCellValue();
                }else {
                    value=cell.getNumericCellValue();
                }
                break;
            default:
                break;
        }
        return value;
    }
//    public static void main(String[] args) throws Exception {
//        InputStream in = new FileInputStream("X:\\专业学习知识总和\\专高6-电商下\\导入测试.xlsx");
//        List<Map> readExcel = readExcel(".xlsx", in);
//        String jsonString = JSON.toJSONString(readExcel);
//        System.out.println(jsonString);
//        JSONArray parseArray = JSON.parseArray(jsonString);
//        System.out.println(parseArray);
//    }

}
